/**
 * lwlsr.com
 * Copyright (c) 2019-2019 All Rights Reserved.
 */
package com.example.util.common;



import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author Jian
 * @version $Id ExcelUtil.java, v 0.1 2019-05-27 16:40 Jian Exp $
 */
@SuppressWarnings("all")
public class ExcelUtil {
    private final static String EXCEL2003 = "xls";
    private final static String EXCEL2007 = "xlsx";

    /**
     * 生成excel
     *
     * @param datas    数据
     * @param header   表头
     * @param folder   文件路径
     * @param fileName 文件名称
     * @return
     */
    public static boolean writeExcel(List<List<String>> datas, List<String> header, String folder, String fileName) {

        if (datas == null || datas.isEmpty() || header == null || header.isEmpty() || StringUtils.isEmpty(folder)
                || StringUtils.isEmpty(fileName)) {
            return false;
        }
        // 判断路径是否正确
        File file = new File(folder);
        if (!file.isDirectory()) {
            return false;
        }
        // 设置文件后缀
        if (!fileName.endsWith(".xlsx")) {
            fileName = fileName + ".xlsx";
        }

        // 第一步，创建一个workbook对应一个excel文件
        Workbook workbook = new XSSFWorkbook();
        // 第二部，在workbook中创建一个sheet对应excel中的sheet
        Sheet sheet = workbook.createSheet("sheet1");
        // 第三部，在sheet表中添加表头第0行，老版本的poi对sheet的行列有限制
        Row row = sheet.createRow(0);
        // 第四步，创建单元格，设置表头
        for (int i = 0; i < header.size(); i++) {
            row.createCell(i).setCellValue(header.get(i));
        }

        // 第五步，写入实体数据，实际应用中这些数据从数据库得到,对象封装数据，集合包对象。对象的属性值对应表的每行的值
        for (int i = 0; i < datas.size(); i++) {
            Row row1 = sheet.createRow(i + 1);
            List<String> data = datas.get(i);
            // 创建单元格设值
            for (int j = 0; j < data.size(); j++) {
                row1.createCell(j).setCellValue(data.get(j));
            }
        }
        // 将文件保存到指定的位置
        try {
            if (!file.exists()) {
                file.mkdirs();
            }
            FileOutputStream fos = new FileOutputStream(folder + "\\" + fileName);
            workbook.write(fos);
            fos.close();
            return true;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return false;
    }


    public static List<List<String>> read(String path) throws IOException {
        List<List<String>> list = new ArrayList<>();
        //1.读取Excel文档对象
        Workbook workbook = null;

        if (path.endsWith(EXCEL2007)) {
            workbook = new XSSFWorkbook(new FileInputStream(path));
        }
        if (path.endsWith(EXCEL2003)) {
            workbook = new HSSFWorkbook(new FileInputStream(path));
        }
        //2.获取要解析的表格（第一个表格）
        Sheet sheet = workbook.getSheetAt(0);
        //获得最后一行的行号
        int lastRowNum = sheet.getLastRowNum();
        //遍历每一行
        for (int i = 0; i <= lastRowNum; i++) {

            List<String> list1 = new ArrayList<>();
            //3.获得要解析的行
            Row row = sheet.getRow(i);
            //4.获得每个单元格中的内容（String）

            short lastCellNum = row.getLastCellNum();
            for (short j = 0; j < lastCellNum; j++) {
                String stringCellValue0="";
                Cell cell = row.getCell(j);
                if (CellType.NUMERIC==cell.getCellTypeEnum()){
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        // 获取日期类型的单元格的值
                        Date d = cell.getDateCellValue();
                        // 进行格式转换
                        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                        stringCellValue0 = formatter.format(d);
                    }
                }else {
                    cell.setCellType(CellType.STRING);
                    stringCellValue0 = cell.getStringCellValue();
                }
                list1.add(stringCellValue0);
            }

            list.add(list1);
        }
        return list;
    }


    public static void main(String[] args) throws IOException {
        List<List<String>> read = read("C:\\Users\\Jian\\Desktop\\字典表导入格式.xls");
        System.out.println(read.get(0).get(8));
    }
}